package faceattendsystem.daoImpl;

import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

import faceattendsystem.dao.IFlowsheetDao;
import faceattendsystem.entity.Employee;
import faceattendsystem.entity.Flowsheet;
import faceattendsystem.entity.PageBean;
import faceattendsystem.util.JDBCTemplate;
import faceattendsystem.util.JDBCTemplate.PreparedStatementSetter;
import faceattendsystem.util.JDBCTemplate.ResultSetCallBack;


public class FlowsheetDaoImpl implements IFlowsheetDao {
	@Override
	public List<Flowsheet> query(Date date, String empid) {
		String sql = "select * from flowsheet where logdate=? and empid=? and result=1 order by logtime";
		
		List<Flowsheet> flowsheetList = JDBCTemplate.query(sql, new PreparedStatementSetter() {
			@Override
			public void setValues(PreparedStatement pstmt) throws SQLException {
				pstmt.setDate(1, date);
				pstmt.setString(2, empid);
			}
		}, new ResultSetCallBack<Flowsheet>() {
			@Override
			public Flowsheet processRs(ResultSet rs) throws SQLException {
				Flowsheet flowsheet = new Flowsheet();
				flowsheet.setLogdate(rs.getDate(1));
				flowsheet.setLogtime(String.valueOf(rs.getInt(2)));
				Employee employee = new Employee();
				employee.setEmpid(rs.getString(3));
				flowsheet.setEmployee(employee);
				flowsheet.setClientdatetime(rs.getString(4));
				flowsheet.setLogip(rs.getString(5));
				flowsheet.setRtn(rs.getInt(6));
				flowsheet.setResult(rs.getString(7));
				flowsheet.setSimilarity(rs.getFloat(8));
				flowsheet.setRequestid(rs.getString(9));
				flowsheet.setResult(rs.getString(10));
				flowsheet.setPicmd5(rs.getString(11));
				return flowsheet;
			}
		});
		
		return flowsheetList;
	}

	@Override
	public Date getMindate(Employee employee) {
		String sql = "select min(logdate) from flowsheet";
		
		if(employee.getPower().equals("1")){
			sql = "select min(logdate) from flowsheet where empid="+employee.getEmpid();
		}
		
		Date mindate = JDBCTemplate.singleQuery(sql, null, new ResultSetCallBack<Date>() {
			@Override
			public Date processRs(ResultSet rs) throws SQLException {
				return rs.getDate(1);
			}
		});
		
		return mindate;
	}

	@Override
	public PageBean<Flowsheet> query(String empid, String bdate, String edate, int pc, int ps) {
		PageBean<Flowsheet> pb = new PageBean<Flowsheet>();
		pb.setPc(pc);
		pb.setPs(ps);
		
		String sql = "select count(*) from flowsheet where empid like '%"+empid+"%' and logdate>='"+bdate+"' and logdate<='"+edate+"'";
		
		Number num = JDBCTemplate.singleQuery(sql, null, new ResultSetCallBack<Number>() {
			@Override
			public Number processRs(ResultSet rs) throws SQLException {
				return rs.getInt(1);
			}
		});
		
		int tr = 1;
		if(num!=null){
			tr = num.intValue();
		}
		pb.setTr(tr);
		
		sql = "select logdate,logtime,employee.empid,clientdatetime,logip,similarity,result from flowsheet,employee where flowsheet.empid=employee.empid and employee.empid like '%"+empid+"%' and logdate>='"+bdate+"' and logdate<='"+edate+"' order by logdate desc,logtime desc,mechid,employee.empid asc limit ?,?";
		
		List<Flowsheet> flowsheetList = JDBCTemplate.query(sql, new PreparedStatementSetter() {
			@Override
			public void setValues(PreparedStatement pstmt) throws SQLException {
				pstmt.setInt(1, (pc-1)*ps);
				pstmt.setInt(2, ps);
			}
		}, new ResultSetCallBack<Flowsheet>() {
			@Override
			public Flowsheet processRs(ResultSet rs) throws SQLException {
				Flowsheet flowsheet = new Flowsheet();
				flowsheet.setLogdate(rs.getDate(1));
				flowsheet.setLogtime(String.valueOf(rs.getInt(2)));
				Employee employee = new Employee();
				employee.setEmpid(rs.getString(3));
				flowsheet.setEmployee(employee);
				flowsheet.setClientdatetime(rs.getString(4));
				flowsheet.setLogip(rs.getString(5));
				flowsheet.setSimilarity(rs.getFloat(6));
				flowsheet.setResult(rs.getString(7));
				return flowsheet;
			}
		});
		
		pb.setBeanList(flowsheetList);
		return pb;
	}

	@Override
	public PageBean<Flowsheet> queryAll(String empid, String bdate, String edate) {
		PageBean<Flowsheet> pb = new PageBean<Flowsheet>();
		
		String sql = "select logdate,logtime,employee.empid,clientdatetime,logip,similarity,result from flowsheet,employee where flowsheet.empid=employee.empid and employee.empid like '%"+empid+"%' and logdate>='"+bdate+"' and logdate<='"+edate+"' order by logdate desc,logtime desc,mechid,employee.empid asc";
		
		List<Flowsheet> flowsheetList = JDBCTemplate.query(sql, null, new ResultSetCallBack<Flowsheet>() {
			@Override
			public Flowsheet processRs(ResultSet rs) throws SQLException {
				Flowsheet flowsheet = new Flowsheet();
				flowsheet.setLogdate(rs.getDate(1));
				flowsheet.setLogtime(String.valueOf(rs.getInt(2)));
				Employee employee = new Employee();
				employee.setEmpid(rs.getString(3));
				flowsheet.setEmployee(employee);
				flowsheet.setClientdatetime(rs.getString(4));
				flowsheet.setLogip(rs.getString(5));
				flowsheet.setSimilarity(rs.getFloat(6));
				flowsheet.setResult(rs.getString(7));
				return flowsheet;
			}
		});
		
		pb.setBeanList(flowsheetList);
		return pb;
	}
}
